# _*_ coding:utf-8 _*_
# @File  : postion.py
# @Time  : 2022-03-21
# @Author: zizle
import datetime

import pandas as pd
from fastapi import APIRouter, Query
from db import FAConnection
from v1_all_api.all_utils import datetime_utils, number_utils
from v1_all_api.all_response import AllResponse

position_api = APIRouter()


@position_api.get('/rank20/')   # 前20持仓分析
def twenty_rank(start_date: str = Query(..., min_length=10, max_length=10),
                end_date: str = Query(..., min_length=10, max_length=10),
                v: str = Query(None, max_length=6, min_length=1)):
    start_ts = datetime_utils.auth_datetime_string(datetime_string=start_date, f='%Y-%m-%d', ts=True)
    end_ts = datetime_utils.auth_datetime_string(datetime_string=end_date, f='%Y-%m-%d', ts=True)
    if not start_ts or not end_ts:
        return AllResponse.validate_error()
    variety_en = v if v else '0'
    db_conn = FAConnection()
    # 查询开始的日期，比查询日期小的最大日期
    ts_sql = 'SELECT MAX(quotes_ts) As ts FROM dat_futures_price_position ' \
             'WHERE quotes_ts<(SELECT MAX(quotes_ts) FROM dat_futures_price_position WHERE quotes_ts<=%s);'
    ts_obj = db_conn.query(ts_sql, (start_ts, ), fetchone=True, keep_conn=True)[0]
    if start_ts > ts_obj['ts']:  # 至少保证2天的数据可以计算差
        start_ts = ts_obj['ts']
    sql = 'SELECT t.quotes_ts,t.variety_en,t.long_position,t.short_position,v.variety_name ' \
          'FROM dat_futures_price_position AS t ' \
          'INNER JOIN ruizy_variety As v ON t.variety_en=v.variety_en ' \
          'WHERE t.quotes_ts>=%s AND t.quotes_ts<=%s AND t.variety_en=t.contract AND IF("0"=%s,TRUE,t.variety_en=%s);'

    df = pd.DataFrame(db_conn.query(sql, param=(start_ts, end_ts, variety_en, variety_en)))
    if df.empty:
        return AllResponse.operate_successfully(data=[])

    df['quote_date'] = df['quotes_ts'].apply(lambda x: datetime.date.fromtimestamp(x))
    df['net_position'] = df['long_position'] - df['short_position']
    df.sort_values(by=['quotes_ts', 'variety_en'], inplace=True)
    del df['quotes_ts']

    ret_df = pd.DataFrame()

    for variety in df.groupby('variety_en').groups:
        vdf = df[df['variety_en'] == variety]
        if vdf.shape[0] < 2:
            vdf['long_diff'] = 0
            vdf['short_diff'] = 0
            vdf['net_diff'] = 0
        else:
            vdf['long_diff'] = vdf['long_position'] - vdf['long_position'].shift(1)
            vdf['short_diff'] = vdf['short_position'] - vdf['short_position'].shift(1)
            vdf['net_diff'] = vdf['net_position'] - vdf['net_position'].shift(1)
        ret_df = pd.concat([ret_df, vdf])

    ret_df.dropna(inplace=True, axis=0)

    ret_df['net_position'] = ret_df['net_position'].apply(number_utils.no_point_zero)
    ret_df['long_diff'] = ret_df['long_diff'].apply(number_utils.no_point_zero)
    ret_df['short_diff'] = ret_df['short_diff'].apply(number_utils.no_point_zero)
    ret_df['net_diff'] = ret_df['net_diff'].apply(number_utils.no_point_zero)

    quote_date_list = list(set(ret_df['quote_date'].tolist()))
    quote_date_list.sort(reverse=True)  # 反序，方便处理显示x天前
    response_data = {
        'rank_data': ret_df.to_dict(orient='records'),
        'date_list': quote_date_list
    }
    return AllResponse.operate_successfully(data=response_data)


@position_api.get('/rank20/totalVolume/')  # 前20名数据合计
def total_twenty_rank(start_date: str = Query(...), v: str = Query(...)):
    start_ts = datetime_utils.auth_datetime_string(start_date, f='%Y-%m-%d', ts=True)
    if not start_ts:
        return AllResponse.validate_error(msg='日期格式错误!')
    # 仅支持查询15天
    end_ts = int((datetime.datetime.fromtimestamp(int(start_ts)) + datetime.timedelta(days=15)).timestamp())
    with FAConnection() as cursor:
        # 查询品种属于哪个交易所
        cursor.execute("SELECT id,variety_en,variety_name,exchange_name FROM ruizy_variety WHERE variety_en=%s LIMIT 1;",
                       [v])
        variety = cursor.fetchone()
        if not variety:
            return AllResponse.validate_error(msg='品种不存在!')
        exchange_name = variety['exchange_name']
        if exchange_name == '郑商所':
            # print('查询郑商所品种前20排名统计')
            cursor.execute("""
                SELECT FROM_UNIXTIME(rank_ts,'%%Y-%%m-%%d') AS rank_date,variety_en,
                SUM(trade) AS t_trade, SUM(long_position) AS t_long_position,
                SUM(short_position) AS t_short_position 
                FROM dat_futures_daily_rank WHERE rank_ts>=%s AND rank_ts<=%s AND variety_en=%s AND variety_en=contract GROUP BY variety_en,rank_ts;
            """, [start_ts, end_ts, v])
        else:
            # print('查询其他交易所品种前20排名统计')
            cursor.execute("""
                SELECT FROM_UNIXTIME(rank_ts,'%%Y-%%m-%%d') AS rank_date,variety_en,
                SUM(trade) AS t_trade, SUM(long_position) AS t_long_position,
                SUM(short_position) AS t_short_position 
                FROM dat_futures_daily_rank WHERE rank_ts>=%s AND rank_ts<=%s AND variety_en=%s GROUP BY variety_en,rank_ts;
            """, [start_ts, end_ts, v])

        ret_list = cursor.fetchall()
        date_list = [r['rank_date'] for r in ret_list]
        start_date = min(date_list)
        end_date = max(date_list)
    return AllResponse.operate_successfully(data={'data_list': ret_list, 'variety_en': v,
                                                  'variety_name': variety['variety_name'],
                                                  'start_date': start_date, 'end_date': end_date})


@position_api.get('/rank20/startDate/')  # 查询排名数据的开始日期
def total_quote_start(v: str = Query(...), ts: int = Query(None)):
    with FAConnection() as cursor:
        # 查询品种属于哪个交易所
        cursor.execute(
            "SELECT id,variety_en,variety_name,exchange_name FROM ruizy_variety WHERE variety_en=%s LIMIT 1;",
            [v])
        variety = cursor.fetchone()
        if not variety:
            return AllResponse.validate_error(msg='品种不存在!')
        exchange_name = variety['exchange_name']
        if exchange_name == '郑商所':
            print('查询郑商所品种前20排名起始日')
            sql = """
                SELECT FROM_UNIXTIME(MIN(rank_ts),'%%Y-%%m-%%d') AS start_date FROM dat_futures_daily_rank
                WHERE variety_en=%s AND variety_en=contract ORDER BY rank_ts;
            """
            prm = [v]
            if ts:
                sql = """
                    SELECT FROM_UNIXTIME(MIN(rank_ts),'%%Y-%%m-%%d') AS start_date FROM dat_futures_daily_rank
                    WHERE variety_en=%s AND variety_en=contract AND rank_ts>%s ORDER BY rank_ts;
                """
                prm = [v, ts]
        else:
            print('查询其他交易所品种前20排名起始日')
            sql = """
                SELECT FROM_UNIXTIME(MIN(rank_ts),'%%Y-%%m-%%d') AS start_date FROM dat_futures_daily_rank
                WHERE variety_en=%s ORDER BY rank_ts;
            """
            prm = [v]
            if ts:
                sql = """
                    SELECT FROM_UNIXTIME(MIN(rank_ts),'%%Y-%%m-%%d') AS start_date FROM dat_futures_daily_rank
                    WHERE variety_en=%s AND rank_ts>%s ORDER BY rank_ts;
                """
                prm = [v, ts]

        cursor.execute(sql, prm)
        ret = cursor.fetchone()['start_date']
    return AllResponse.operate_successfully(data={'start_date': ret, 'variety_en': v, 'variety_name': variety['variety_name']})